import datetime
from io import BytesIO

from flask import request, Response
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

from tzq.document_management_system import dms_bp
from tzq.document_management_system.model.catalog_model import Catalog, CatalogModify, CatalogDelete


@dms_bp.route('/api/exportCatalogAllDataToExcel', methods=['GET', 'POST'])
def export_catalog_all_data_to_excel():
    # 导出 catalog 的所有数据
    # 入参
    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")

    # 定义Excel表头样式
    header_font = Font(name='Arial', bold=True, color="000000", size=12)
    header_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    center_alignment = Alignment(horizontal="center", vertical="center")

    # 1、导出 catalog 的数据
    query = Catalog.query
    query = query.filter(Catalog.delete_flag == 'N')
    if principal: query = query.filter(Catalog.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(Catalog.online_date == f'{online_date}')
    if created_by: query = query.filter(Catalog.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(Catalog.online_date.desc(), Catalog.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    wb = Workbook()
    ws = wb.active
    ws.title = "SiteNode"
    # 写入表头
    headers = [
        'Parent Node Name',
        'Name',
        'Description',
        'URL',
        'Order',
        'Visible',
        'Open Link Type',
        'Control',
        'AccessName',
        'Location',
        '责任人', '上线日期'
    ]
    ws.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws.append([
            # 数据 - 业务字段
            item.parent_node_name,
            item.name,
            item.description,
            item.url,
            item.order,
            '2',
            item.open_link_type,
            '1',
            'saasoneservice$',
            '',
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
    last_row = ws.max_row
    ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws.column_dimensions['A'].width = 47  # Parent Node Name
    ws.column_dimensions['B'].width = 47  # Name
    ws.column_dimensions['C'].width = 23  # Description
    ws.column_dimensions['D'].width = 47  # URL
    ws.column_dimensions['E'].width = 11  # Order
    ws.column_dimensions['F'].width = 11  # Visible
    ws.column_dimensions['G'].width = 19  # Open Link Type
    ws.column_dimensions['H'].width = 11  # Control
    ws.column_dimensions['I'].width = 17  # AccessName
    ws.column_dimensions['J'].width = 10  # Location
    ws.column_dimensions['K'].width = 13  # 责任人
    ws.column_dimensions['L'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['E', 'F', 'H', 'I', 'L']:
        for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 2、导出 菜单修改 的数据
    query = CatalogModify.query
    query = query.filter(CatalogModify.delete_flag == 'N')
    if principal: query = query.filter(CatalogModify.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(CatalogModify.online_date == f'{online_date}')
    if created_by: query = query.filter(CatalogModify.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(CatalogModify.online_date.desc(), CatalogModify.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    # 创建第二个工作表
    ws2 = wb.create_sheet("菜单修改")
    # 写入表头
    headers = [
        '事情',
        '菜单在界面的位置',
        '操作',
        '在公服平台修改',
        '责任人', '上线日期'
    ]
    ws2.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws2.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws2.append([
            item.event,
            item.saas_location,
            item.operation,
            item.jalor_location,
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws2.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws2.cell(row=1, column=ws2.max_column).column_letter
    last_row = ws2.max_row
    ws2.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws2.column_dimensions['A'].width = 60  # 事情
    ws2.column_dimensions['B'].width = 60  # 菜单在界面的位置
    ws2.column_dimensions['C'].width = 10  # 操作
    ws2.column_dimensions['D'].width = 60  # 在公服平台修改
    ws2.column_dimensions['E'].width = 13  # 责任人
    ws2.column_dimensions['F'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['C', 'F']:
        for row in ws2.iter_rows(min_col=ws2[col + '1'].column, max_col=ws2[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 3、菜单删除
    query = CatalogDelete.query
    query = query.filter(CatalogDelete.delete_flag == 'N')
    if principal: query = query.filter(CatalogDelete.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(CatalogDelete.online_date == f'{online_date}')
    if created_by: query = query.filter(CatalogDelete.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(CatalogDelete.online_date.desc(), CatalogDelete.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    # 创建第三个工作表
    ws3 = wb.create_sheet("菜单删除")
    # 写入表头
    headers = [
        '事情',
        '菜单在界面的位置',
        '操作',
        '在公服平台修改',
        '责任人', '上线日期'
    ]
    ws3.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws3.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws3.append([
            item.event,
            item.saas_location,
            item.operation,
            item.jalor_location,
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws3.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws3.cell(row=1, column=ws3.max_column).column_letter
    last_row = ws3.max_row
    ws3.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws3.column_dimensions['A'].width = 60  # 事情
    ws3.column_dimensions['B'].width = 60  # 菜单在界面的位置
    ws3.column_dimensions['C'].width = 10  # 操作
    ws3.column_dimensions['D'].width = 60  # 在公服平台修改
    ws3.column_dimensions['E'].width = 13  # 责任人
    ws3.column_dimensions['F'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['C', 'F']:
        for row in ws3.iter_rows(min_col=ws3[col + '1'].column, max_col=ws3[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    date_string = datetime.datetime.now().strftime("%Y%m%d%H%M%S")

    # 将工作簿写入 BytesIO 流
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    if online_date:
        version_string = "(v." + online_date + ")-"
    else:
        version_string = "ALL-"

    # 使用 Response 返回 Excel 文件
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={
            "Content-Disposition": "attachment; filename=jalor-SiteNode-" + version_string + date_string + ".xlsx"
            # 文件名不能为中文，不然报错了
        }
    )
